﻿// <copyright file="AppVDatabaseFunctions.cs" company="Sinclair Community College">
// Copyright (c) Sinclair Community College. All rights reserved.
// </copyright>

namespace SinclairCC.AppVSuite.PermissionCopy
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using SinclairCC.AppVSuite.Shared;

    /// <summary>
    /// This class contains functions that manipulate the App-V SQL database.
    /// </summary>
    internal static class AppVDatabaseFunctions
    {
        // TODO: Maybe the functions that take an AppVObjectType object can be split into application functions and application group functions.

        /// <summary>
        /// Gets a list of the application groups contained within a parent application group.
        /// </summary>
        /// <param name="appVDatabaseConnection">
        /// A connection to the App-V SQL database.
        /// </param>
        /// <param name="parentId">
        /// The ID of the parent application group.
        /// </param>
        /// <returns>
        /// Returns a DataTable containing the name and ID of the application groups contained within the
        /// specified parent application group.
        /// </returns>
        public static DataTable GetApplicationGroups(SqlConnection appVDatabaseConnection, int? parentId)
        {
            System.Data.DataTable appGroupData = new DataTable();
            appGroupData.Locale = System.Globalization.CultureInfo.InvariantCulture;
            if (appVDatabaseConnection.State == ConnectionState.Open)
            {
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(string.Format(System.Globalization.CultureInfo.InvariantCulture, "SELECT name, app_group_id FROM APPLICATION_GROUPS WHERE parent_id {0} ORDER BY name, app_group_id", (parentId == null) ? "IS NULL" : string.Format(System.Globalization.CultureInfo.InvariantCulture, " = {0}", parentId.Value)), appVDatabaseConnection);
                adapter.Fill(appGroupData);
                adapter.Dispose();
            }

            return appGroupData;
        }

        /// <summary>
        /// Gets a list of the applications in an application group.
        /// </summary>
        /// <param name="appVDatabaseConnection">
        /// A connection to the App-V SQL database.
        /// </param>
        /// <param name="groupId">
        /// The ID of the application group.
        /// </param>
        /// <param name="recursive">
        /// Indicates whether to include applications that are contained in descendent groups.
        /// </param>
        /// <returns>
        /// Returns a DataTable containing the ID, name, version, and icon file of the applications
        /// contained within the specified application group.
        /// </returns>
        public static DataTable GetApplications(SqlConnection appVDatabaseConnection, int? groupId, bool recursive)
        {
            System.Data.DataTable appData = new DataTable();
            appData.Locale = System.Globalization.CultureInfo.InvariantCulture;
            if (appVDatabaseConnection.State == ConnectionState.Open)
            {
                if (recursive)
                {
                    DataTable childGroupTable = GetApplicationGroups(appVDatabaseConnection, groupId);
                    foreach (DataRow row in childGroupTable.Rows)
                    {
                        int child_group_id = (int)row["app_group_id"];
                        DataTable childGroupApps = GetApplications(appVDatabaseConnection, child_group_id, true);
                        appData.Merge(childGroupApps);
                    }
                }

                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(string.Format(System.Globalization.CultureInfo.InvariantCulture, "SELECT app_id, name, version, icon_file FROM APPLICATIONS WHERE app_group_id {0} ORDER BY name, app_id", (groupId == null) ? "IS NULL" : string.Format(System.Globalization.CultureInfo.InvariantCulture, " = {0}", groupId.Value)), appVDatabaseConnection);
                adapter.Fill(appData);
                adapter.Dispose();
            }

            return appData;
        }

        /// <summary>
        /// Gets a list of the security groups that have access to a specified application.
        /// </summary>
        /// <param name="appVDatabaseConnection">
        /// A connection to the App-V SQL database.
        /// </param>
        /// <param name="appId">
        /// The ID of the application for which the list of security groups is to be retrieved.
        /// </param>
        /// <returns>
        /// Returns an array of strings containing the SIDs of the security groups that have access to the application.
        /// </returns>
        public static string[] GetApplicationAssignments(SqlConnection appVDatabaseConnection, int appId)
        {
            string[] returnArray = null;
            System.Data.DataTable appAssignments = new DataTable();
            appAssignments.Locale = System.Globalization.CultureInfo.InvariantCulture;
            if (appVDatabaseConnection.State == ConnectionState.Open)
            {
                string query = "SELECT DISTINCT group_ref FROM APPLICATION_ASSIGNMENTS WHERE (app_id = {0}) ORDER BY group_ref";
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(string.Format(System.Globalization.CultureInfo.InvariantCulture, query, appId), appVDatabaseConnection);
                adapter.Fill(appAssignments);
                adapter.Dispose();

                List<string> group_refs = new List<string>();
                foreach (DataRow row in appAssignments.Rows)
                {
                    string group_ref = (string)row["group_ref"];
                    group_ref = group_ref.ToUpper(System.Globalization.CultureInfo.InvariantCulture);
                    if (!group_refs.Contains(group_ref))
                    {
                        group_refs.Add(group_ref);
                    }
                }

                returnArray = new string[group_refs.Count];
                group_refs.CopyTo(returnArray);
            }

            return returnArray;
        }

        /// <summary>
        /// Grants access to an application or application group for the specified security groups.
        /// </summary>
        /// <param name="groupRefs">
        /// A list of the SIDs of the security groups to which access is to be granted.
        /// </param>
        /// <param name="objectType">
        /// Indicates the type of App-V object to which access is to be granted.
        /// </param>
        /// <param name="objectId">
        /// The unique ID of the application or application group in the App-V database.
        /// </param>
        public static void AddAccessToApp(System.Collections.ObjectModel.Collection<string> groupRefs, AppVObjectType objectType, int objectId)
        {
            System.Data.SqlClient.SqlConnection connAppV = new System.Data.SqlClient.SqlConnection(Settings.ConnectionString);
            connAppV.Open();

            if (connAppV.State == ConnectionState.Open)
            {
                if (objectType == AppVObjectType.ApplicationGroup)
                {                    
                    DataTable appsTable = AppVDatabaseFunctions.GetApplications(connAppV, objectId, true);
                    List<int> apps = new List<int>(appsTable.Rows.Count);
                    foreach (DataRow row in appsTable.Rows)
                    {
                        int app_id = (int)row["app_id"];
                        if (!apps.Contains(app_id))
                        {
                            apps.Add(app_id);
                        }
                    }

                    AddAccessToApp(connAppV, groupRefs, apps);
                }
                else
                {
                    AddAccessToApp(connAppV, groupRefs, objectId);
                }
            }

            if (connAppV != null)
            {
                if (connAppV.State == System.Data.ConnectionState.Open)
                {
                    connAppV.Close();
                }

                connAppV.Dispose();
            }
        }

        /// <summary>
        /// Delets all access to the specified application or application group.
        /// </summary>
        /// <param name="objectType">
        /// Indicates the type of App-V object to which access is to be granted.
        /// </param>
        /// <param name="objectId">
        /// The unique ID of the application or application group in the App-V database.
        /// </param>
        public static void DeleteAllAccessToApps(AppVObjectType objectType, int objectId)
        {
            System.Data.SqlClient.SqlConnection connAppV = new System.Data.SqlClient.SqlConnection(Settings.ConnectionString);
            connAppV.Open();

            if (connAppV.State == ConnectionState.Open)
            {
                if (objectType == AppVObjectType.ApplicationGroup)
                {
                    DataTable appsTable = AppVDatabaseFunctions.GetApplications(connAppV, objectId, true);
                    List<int> apps = new List<int>(appsTable.Rows.Count);
                    foreach (DataRow row in appsTable.Rows)
                    {
                        int app_id = (int)row["app_id"];
                        if (!apps.Contains(app_id))
                        {
                            apps.Add(app_id);
                        }
                    }

                    DeleteAllAccessToApps(connAppV, apps);
                }
                else
                {
                    DeleteAllAccessToApps(connAppV, objectId);
                }
            }

            if (connAppV != null)
            {
                if (connAppV.State == System.Data.ConnectionState.Open)
                {
                    connAppV.Close();
                }

                connAppV.Dispose();
            }
        }

        /// <summary>
        /// Grants access to an application for the specified security groups.
        /// </summary>
        /// <param name="appVDatabaseConnection">
        /// A connection to the App-V SQL database.
        /// </param>
        /// <param name="groupRefs">
        /// A list of the SIDs of the security groups to which access is to be granted.
        /// </param>
        /// <param name="appId">
        /// The ID of the application to which access is to be granted.
        /// </param>
        private static void AddAccessToApp(System.Data.SqlClient.SqlConnection appVDatabaseConnection, System.Collections.ObjectModel.Collection<string> groupRefs, int appId)
        {
            AddAccessToApp(appVDatabaseConnection, groupRefs, new System.Collections.Generic.List<int>() { appId });
        }

        /// <summary>
        /// Grants access to a set of applications for the specified security groups.
        /// </summary>
        /// <param name="appVDatabaseConnection">
        /// A connection to the App-V SQL database.
        /// </param>
        /// <param name="groupRefs">
        /// A list of the SIDs of the security groups to which access is to be granted.
        /// </param>
        /// <param name="appIds">
        /// A list of the IDs of the applications to which access is to be granted.
        /// </param>
        private static void AddAccessToApp(System.Data.SqlClient.SqlConnection appVDatabaseConnection, System.Collections.ObjectModel.Collection<string> groupRefs, System.Collections.Generic.List<int> appIds)
        {
            if (appVDatabaseConnection.State == ConnectionState.Open)
            {
                System.Data.SqlClient.SqlCommand insertAssignmentCommand = appVDatabaseConnection.CreateCommand();
                insertAssignmentCommand.CommandText = "INSERT INTO APPLICATION_ASSIGNMENTS (app_id, group_ref) VALUES (@app_id, @group_ref)";
                insertAssignmentCommand.Parameters.Add("@app_id", SqlDbType.Int);
                insertAssignmentCommand.Parameters.Add("@group_ref", SqlDbType.NVarChar, 256);

                foreach (int i in appIds)
                {
                    // Build a list of the security group SIDs that need to be added to the application by eliminating those
                    // security groups that already have access.
                    System.Collections.Generic.List<string> groupsToBeAdded = new System.Collections.Generic.List<string>(groupRefs);
                    string[] assignedGroups = AppVDatabaseFunctions.GetApplicationAssignments(appVDatabaseConnection, i);
                    foreach (string group_ref in assignedGroups)
                    {
                        for (int j = 0; j < groupsToBeAdded.Count; j++)
                        {
                            if (string.Compare(group_ref, groupsToBeAdded[j], System.StringComparison.InvariantCultureIgnoreCase) == 0)
                            {
                                groupsToBeAdded.RemoveAt(j);
                                j--;
                            }
                        }
                    }

                    // Add the security group SIDs that need to be added.
                    using (System.Data.SqlClient.SqlTransaction trans = appVDatabaseConnection.BeginTransaction())
                    {
                        insertAssignmentCommand.Transaction = trans;
                        foreach (string group_ref in groupsToBeAdded)
                        {
                            insertAssignmentCommand.Parameters["@app_id"].Value = i;
                            insertAssignmentCommand.Parameters["@group_ref"].Value = group_ref;
                            insertAssignmentCommand.ExecuteScalar();
                        }

                        trans.Commit();
                    }
                }

                insertAssignmentCommand.Dispose();
            }
        }

        /// <summary>
        /// Removes all access permissions for an application.
        /// </summary>
        /// <param name="appVDatabaseConnection">
        /// A connection to the App-V SQL database.
        /// </param>
        /// <param name="appId">
        /// The ID of the application for which access is to be removed.
        /// </param>
        private static void DeleteAllAccessToApps(System.Data.SqlClient.SqlConnection appVDatabaseConnection, int appId)
        {
            DeleteAllAccessToApps(appVDatabaseConnection, new System.Collections.Generic.List<int>() { appId });
        }

        /// <summary>
        /// Removes all access permissions for a set of applications.
        /// </summary>
        /// <param name="appVDatabaseConnection">
        /// A connection to the App-V SQL database.
        /// </param>
        /// <param name="appIds">
        /// A list of the IDs of the applications for which access is to be removed.
        /// </param>
        private static void DeleteAllAccessToApps(System.Data.SqlClient.SqlConnection appVDatabaseConnection, System.Collections.Generic.List<int> appIds)
        {
            if (appVDatabaseConnection.State == ConnectionState.Open)
            {
                System.Data.SqlClient.SqlCommand deleteAssignmentCommand = appVDatabaseConnection.CreateCommand();

                deleteAssignmentCommand.CommandText = "DELETE FROM APPLICATION_ASSIGNMENTS WHERE (app_id = @app_id)";

                deleteAssignmentCommand.Parameters.Add("@app_id", SqlDbType.Int);

                System.Data.SqlClient.SqlTransaction trans = appVDatabaseConnection.BeginTransaction();
                deleteAssignmentCommand.Transaction = trans;

                foreach (int i in appIds)
                {
                    deleteAssignmentCommand.Parameters["@app_id"].Value = i;
                    deleteAssignmentCommand.ExecuteScalar();
                }

                trans.Commit();
                trans.Dispose();
                deleteAssignmentCommand.Dispose();
            }
        }
    }
}
